package core;
//GENERATION DE HASHMAP DU TYPE ::
//[User][id][index][type] = type
//[User][id][index][value] = value
//[User][id][index][FK] = true/false
//[User][id][index][PK] = true/false
//[User][id][index][upRule] = true/false
//[User][id][index][delRule] = true/false

// Retour soutenance
// Ne pas garder d'objets specifiques a un client.
// Passer le DbManager en Singleton.
// Passer au stateLess.

// Ca craind de devoir refoutre les try catch a chaque methode, pourquoi ca remonte pas ?

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class DbManager {
	
	private Connection			co;
	private PreparedStatement 	st;
	
	private HashMap<Integer, HashMap<String, String>>	depends = new HashMap<Integer, HashMap<String, String>>();
	private HashMap<String, ArrayList<String>>	toCall = new HashMap<String, ArrayList<String>>();
	private HashMap<String, String>			columnRelation = new HashMap<String, String>();
	private String	toLoad = new String();
	private	static DbManager _inst = null;
    
    // Constructor
	private DbManager(){
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			String conString = "jdbc:mysql://localhost:3306/java";
			co = DriverManager.getConnection(conString, "root", ""); //login & password
		}	
		catch (SQLException e) 				{System.out.println("dbManager SQLException: " + e.toString()); e.printStackTrace();}	
		catch (Exception e) 				{System.out.println("dbManager Exception: " + e.toString()); 	e.printStackTrace();}
	}
	
	public static DbManager	getInstance(){
		if (_inst == null)
			_inst = new DbManager();
		return(_inst);
	}
	
	public HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>>		loadLightObject(){
		String 	query = new String();
		query = "SELECT * FROM `"+toLoad+"`;";
		//System.out.println(query);
		//System.out.println("--------------------------\r\n"+query);
		HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>> mystObject = loadObject(query);
		//System.out.println(mystObject+"\r\n--------------------------");
		return (mystObject);
	}
	
	public HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>>		
	loadHeavyObject(ArrayList<HashMap<String, String>> whereClause, ArrayList<HashMap<String, String>> groupClause)
	{
		String 	query = new String();
		query = "SELECT ";
		System.out.println(toCall);
		for (String k : toCall.keySet()){
			for(int i=0;i<toCall.get(k).size();i++){
				columnRelation.put(toCall.get(k).get(i), k+"_"+toCall.get(k).get(i));
				//System.out.println(columnRelation);
				query += "`"+k+"`."+toCall.get(k).get(i)+" AS '"+k+"_"+toCall.get(k).get(i)+"', ";
			}
		}
		
		query = query.substring(0, (query.length()-2));
		query+= "FROM `"+toLoad+"` ";
		for (int i=0;i<depends.size();i++){
			if (depends.get(i).containsKey("FKCOLUMN_NAME"))
				query += "LEFT JOIN `"+depends.get(i).get("FKTABLERELATION_NAME")+"` ON (`"+depends.get(i).get("FKTABLERELATION_NAME")+"`."+depends.get(i).get("FKCOLUMNRELATION_NAME")+"=`"+depends.get(i).get("FKTABLE_NAME")+"`."+depends.get(i).get("FKCOLUMN_NAME")+") ";
		}
		query += (whereClause.size()>0) ? "WHERE " : "";
		for(int index=0;index<whereClause.size();index++){
			System.out.println(whereClause.get(index).get("value"));
			query += whereClause.get(index).get("value");
			if ((index+1<whereClause.size()) && (whereClause.get(index+1).get("type")!="LINKER"))
				query += "AND ";
		}
		for(int index=0;index<groupClause.size();index++)
			query += groupClause.get(index).get("value");
		query += ";";
		System.out.println("--------------------------");
		HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>> mystObject = loadObject(query);
		System.out.println(mystObject+"\r\n--------------------------");
		return (mystObject);
	}
	
	protected void finalize() throws Throwable {
		try {st.close();}
		catch (java.sql.SQLException e)	{System.out.println("dbDisconnect: close statement: " + e.toString());}
		catch (Exception e)	{System.out.println("dbDisconnect: close statement: " + e.toString());}		
		
		try {co.close();}
		catch (java.sql.SQLException e)	{System.out.println("dbDisconnect: close statement: " + e.toString());}
		catch (Exception e)	{System.out.println("dbDisconnect: close connection: " + e.toString());}
		
		super.finalize();
	}
	
	private HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>>	loadObject(String query){
		HashMap<String, HashMap<String, HashMap<Integer, HashMap<String, String>>>> hash_schema = new HashMap<String, HashMap<String,HashMap<Integer,HashMap<String,String>>>>();
		try
		{
			HashMap<String, HashMap<Integer, HashMap<String, String>>> hash_c;
			HashMap<String, String> hash_tmp;
			HashMap<Integer, HashMap<String, String>> hash_b;
				
			ResultSet rs = null;
			
			try{
				System.out.println(query.replace("[PKID]", depends.get(0).get("PKCOLUMN_NAME")));
				st = co.prepareStatement(query.replace("[PKID]", depends.get(0).get("PKCOLUMN_NAME")));
				rs = st.executeQuery();
			}catch(java.sql.SQLException e){
				System.out.println(e.toString());
			}
			ResultSetMetaData rsmd	= rs.getMetaData(); // Recuperation des metas
			hash_c = new HashMap<String, HashMap<Integer, HashMap<String, String>>>();
			for (int index=0;index<columnRelation.size();index++){
				hash_b = new HashMap<Integer, HashMap<String, String>>();
				rs.first();rs.previous();int i=0;
				while(rs.next()){
					hash_tmp = new HashMap<String, String>();
					hash_tmp.put("type", rsmd.getColumnClassName(index + 1));
					hash_tmp.put("value", rs.getString(index+1));
					
					hash_b.put(i++, hash_tmp);
				}
				hash_c.put(columnRelation.get(rsmd.getColumnName(index + 1))!=null?columnRelation.get(rsmd.getColumnName(index + 1)):rsmd.getColumnName(index + 1), hash_b);
			}
			hash_schema.put(toLoad, hash_c);
		}	
		catch (SQLException e) 				{System.out.println("loadObject SQLException: " + e.toString()); e.printStackTrace();}	
		catch (Exception e) 				{System.out.println("loadObject Exception: " + e.toString()); 	e.printStackTrace();}
		
		return (hash_schema);
	}

	/*____METHODE DE CUSTOMISATION DE REQUETES____*/
	
	public DbManager	addTable(String table){
		try
		{
			if (toLoad.isEmpty() || toLoad == "")
			{
				toLoad = table;
				DatabaseMetaData dmd = co.getMetaData();
			
				/*__ RECUPERATION DES DEPENDANCES DE LA TABLE __*/
				
				ResultSet x = dmd.getColumns(null, null, toLoad, null);
				ArrayList<String> c = new ArrayList<String>();
				toCall.put(toLoad, c);
				while (x.next()){
					toCall.get(toLoad).add(x.getString(4));
				}

				ResultSet	pk = dmd.getPrimaryKeys(null, null, toLoad);
				int index = 0;
				while (pk.next()){
					HashMap<String, String> 	tmp_ = new HashMap<String, String>();
					tmp_.put("PKTABLE_NAME", pk.getString(3));
					tmp_.put("PKCOLUMN_NAME", pk.getString(4));

					System.out.println("PKTABLE_NAME = "+pk.getString(3));
					System.out.println("PKCOLUMN_NAME = "+pk.getString(4));
					
					x = dmd.getColumns(null, null, tmp_.get("PKTABLE_NAME"), null);
			        ArrayList<String> tmp = new ArrayList<String>();
					toCall.put(tmp_.get("PKTABLE_NAME"), tmp);
					while (x.next())
						toCall.get(tmp_.get("PKTABLE_NAME")).add(x.getString(4));
					depends.put(index, tmp_);
					index++;
				}
				System.out.println("DEPENDS1 :: "+depends);
				index = 0;
				ResultSet depend = dmd.getImportedKeys(null, null, toLoad);
				while (depend.next()){
					HashMap<String, String> 	tmp_ = new HashMap<String, String>();
					
					if (depends.containsKey(index))
						tmp_ = depends.get(index);
					
			        tmp_.put("FKTABLE_NAME", depend.getString("FKTABLE_NAME"));
			        tmp_.put("FKCOLUMN_NAME", depend.getString("FKCOLUMN_NAME"));
			        
			        tmp_.put("FKTABLERELATION_NAME", depend.getString("PKTABLE_NAME"));
			        tmp_.put("FKCOLUMNRELATION_NAME", depend.getString("PKCOLUMN_NAME"));
			        
			        tmp_.put("UPDATE_RULE", new Integer(depend.getShort("UPDATE_RULE")).toString());
			        tmp_.put("DELETE_RULE", new Integer(depend.getShort("DELETE_RULE")).toString());
			        
			        System.out.println("FKTABLE_NAME = "+depend.getString("FKTABLE_NAME"));
					System.out.println("FKCOLUMN_NAME = "+depend.getString("FKCOLUMN_NAME"));
					System.out.println("FKTABLERELATION_NAME = "+depend.getString("PKTABLE_NAME"));
					System.out.println("FKCOLUMNRELATION_NAME = "+depend.getString("PKCOLUMN_NAME"));
			        x = dmd.getColumns(null, null, tmp_.get("FKTABLERELATION_NAME"), null);
			        ArrayList<String> tmp = new ArrayList<String>();
					toCall.put(tmp_.get("FKTABLERELATION_NAME"), tmp);
					while (x.next()){
						toCall.get(tmp_.get("FKTABLERELATION_NAME")).add(x.getString(4));
					}
					depends.put(index, tmp_);
					index++;
				}
			}
			System.out.println("DEPENDS :: "+depends);
		}	
		catch (SQLException e) 				{System.out.println("dbConnect SQLException: " + e.toString()); e.printStackTrace();}	
		catch (Exception e) 				{System.out.println("dbConnect Exception: " + e.toString()); 	e.printStackTrace();}
		
		return (this);
	}
}
	